/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package pkg_group5;

import java.sql.*;
import java.util.ArrayList;
/**
 *
 * @author Matt
 */
public class FeedbackDatabaseFunctions extends Database {

    public FeedbackDatabaseFunctions()
    {
        super("Feedback");
    }

    //setup a new feedback system for each group, stops them seeing each others feedback.
    public int setupFeedbackSystem(String username, String password) {

        String query = "INSERT INTO FeedbackSystem (username, password) VALUES (?, ?)";

        try {
            PreparedStatement ps = conn.prepareStatement(query);
            ps.clearParameters();
            ps.setString(1, username);
            ps.setString(2, password);
            ps.executeUpdate();
            return 0;
        } catch (java.sql.SQLException e) {
            System.out.println(e);
        }
        return -1;
    }

    public int loginToFeedback(String username, String password) {
        String query = "SELECT FeedbackID FROM FeedbackSystem WHERE FeedbackSystemName =? AND Password =?";

        try {
            PreparedStatement ps = conn.prepareStatement(query);
            ps.clearParameters();
            ps.setString(1, username);
            ps.setString(2, password);

            ResultSet rs = ps.executeQuery();
            boolean isEmpty = true; //Empty set to true

            while (rs.next()) {
                isEmpty = false; //Results found..
                return rs.getInt(1);
            }

            if (isEmpty) {
                return -1;
            }
        } catch (Exception e) {
            System.out.println(e);
        }
        return -1;
    }

    public ArrayList<FeedbackItem> getFeedback(int FeedbackID, int BookID) {

        String query = "Select * from Feedback Where FeedbackID = ? AND BookID = ?";

        ArrayList<FeedbackItem> feedbackList = new ArrayList<FeedbackItem>();
        FeedbackItem item = new FeedbackItem();

        try {
            PreparedStatement ps = conn.prepareStatement(query);
            ps.clearParameters();
            ps.setInt(1, FeedbackID);
            ps.setInt(2, BookID);

            ResultSet rs = ps.executeQuery();

            while (rs.next()) {
                item.setBookID  (rs.getInt("BookID"));
                item.setID      (rs.getInt("ID"));
                item.setSystemID(rs.getInt("FeedbackID"));
                item.setUsername(rs.getString("username"));
                item.setFeedback(rs.getString("feedback"));

                feedbackList.add(item);
            }
        } catch (Exception e) {
            System.out.println(e);
        }

        return feedbackList;
    }

    public int giveFeedback(int BookID, int SystemID, String username, String feedback)
    {
        String query = "INSERT INTO Feedback (SystemID, bookID, username, feedback) VALUES (?, ?, ?, ?)";

        try {
            PreparedStatement ps = conn.prepareStatement(query);
            ps.clearParameters();
            ps.setInt(1, SystemID);
            ps.setInt(2, BookID);
            ps.setString(3, username);
            ps.setString(4, feedback);
            ps.executeUpdate();
            return 0;
        } catch (java.sql.SQLException e) {
            System.out.println(e);
        }
        return -1;
    }
}
